﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ET.Monitor.DAL
{
    public class AssetsDAL
    {
        string strSql = "", strErrMsg = "";
        object obj = null;
        DataTable dt = new DataTable();


        InvokeWebService iw = new InvokeWebService();
        string rtdbRelationAddress = "";
        string rtdbRealTimeAddress = "";
        string rtdbNameSpace = "";

        public AssetsDAL()
        {
            rtdbRelationAddress = ConfigurationManager.ConnectionStrings["RelationURL"].ConnectionString;
            rtdbRealTimeAddress = ConfigurationManager.ConnectionStrings["RealTimeURL"].ConnectionString;
            rtdbNameSpace = ConfigurationManager.ConnectionStrings["NameSpace"].ConnectionString;
        }

        #region 获得ztree的设备
        public DataTable Eqpnameforztree()
        {
            string eqpnforzt = "select T_TYPEID, T_TYPEDESC_EN, T_TYPEDESC_CN FROM T_BASE_EQUIPTYPE";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region Inventory查询
        public DataTable InstockSearch(string typeid)
        {
            string instocksrae = @"SELECT TB.T_ORGDESC_CN, TB.T_ORGDESC_EN, B.T_COMPANYDESC_EN, B.T_COMPANYDESC_CN, B.T_MACHINEID, A.D_QUANTITY, B.T_TYPEID
                                FROM dbo.T_INFO_INVENTORY A INNER JOIN (SELECT T_MACHINEID, M.T_TYPEID, T_COMPANYDESC_EN, T_COMPANYDESC_CN
	                                    FROM dbo.T_BASE_MACHINE M INNER JOIN dbo.T_BASE_MACHINECOMPANY MC ON M.T_COMPANYID = MC.T_COMPANYID
	                                    INNER JOIN dbo.T_BASE_EQUIPTYPE ET ON M.T_TYPEID = ET.T_TYPEID ) B ON A.T_MACHINEID = B.T_MACHINEID 
                                        INNER JOIN T_BASE_ORG TB ON A.T_ORGID = TB.T_ORGID ";
            if (!string.IsNullOrEmpty(typeid))
            {
                instocksrae += "where B.T_TYPEID='" + typeid + "'";
            }
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { instocksrae, strErrMsg });
            return dt;
        }

        public DataTable InstockSearchOrg(string id)
        {
            string instocksrae = @"SELECT TB.T_ORGDESC_CN, TB.T_ORGDESC_EN, B.T_COMPANYDESC_EN, B.T_COMPANYDESC_CN, B.T_MACHINEID, A.D_QUANTITY, B.T_TYPEID
                                FROM dbo.T_INFO_INVENTORY A INNER JOIN (SELECT T_MACHINEID, M.T_TYPEID, T_COMPANYDESC_EN, T_COMPANYDESC_CN
	                                    FROM dbo.T_BASE_MACHINE M INNER JOIN dbo.T_BASE_MACHINECOMPANY MC ON M.T_COMPANYID = MC.T_COMPANYID
	                                    INNER JOIN dbo.T_BASE_EQUIPTYPE ET ON M.T_TYPEID = ET.T_TYPEID ) B ON A.T_MACHINEID = B.T_MACHINEID 
                                        INNER JOIN T_BASE_ORG TB ON A.T_ORGID = TB.T_ORGID ";
            if (!string.IsNullOrEmpty(id))
            {
                instocksrae += "where TB.T_ORGID='" + id + "'";
            }
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { instocksrae, strErrMsg });
            return dt;
        }
        #endregion

        #region Instock, Outstock

        public bool Instock(string orgid, DateTime ttime, string uname, int qtty, string machid, float pce, float period, DateTime wattyme, string ctctname, string ctctphone, string ctctmail)
        {
            string StockInsert = @"INSERT INTO T_INFO_INSTOCK(T_ORGID, T_TIME, T_USERNAME, D_QUANTITY, T_MACHINEID, D_PRICE, D_WTPERIOD, T_WTDATE, T_CONTACTNAME, T_CONTACTPHONE, T_CONTACTEMAIL) VALUES ('" + orgid + "','" + ttime + "','" + uname + "'," + qtty + ",'" + machid + "'," + pce + "," + period + ",'" + wattyme + "','" + ctctname + "','" + ctctphone + "','" + ctctmail + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { StockInsert, strErrMsg });
        }
        public bool Outstock(string orgid, DateTime ttime, int qtty, string uname, string machid)
        {
            string OutstockInven = @"INSERT INTO T_INFO_OUTSTOCK(T_ORGID, T_TIME, D_QUANTITY, T_USERNAME, T_MACHINEID)values('" + orgid + "','" + ttime + "'," + qtty + ",'" + uname + "','" + machid + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { OutstockInven, strErrMsg });
        }

        public bool InsertInventory(string orgid, int qtty, string machid)
        {
            string InventoryInsert = @"INSERT INTO T_INFO_INVENTORY(T_ORGID, D_QUANTITY, T_MACHINEID) VALUES ('" + orgid + "'," + qtty + ",'" + machid + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { InventoryInsert, strErrMsg });
        }

        public bool UpdateInventory(string orgid, int qtty, string machid)
        {
            string Inventoryupdate = @"UPDATE T_INFO_INVENTORY SET D_QUANTITY=D_QUANTITY+" + qtty + " WHERE T_ORGID='" + orgid + "' AND T_MACHINEID='" + machid + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { Inventoryupdate, strErrMsg });
        }

        public bool UpdateInventoryOut(string orgid, int qtty, string machid)
        {
            string Inventoryupdate = @"UPDATE T_INFO_INVENTORY SET D_QUANTITY=D_QUANTITY-" + qtty + " WHERE T_ORGID='" + orgid + "' AND T_MACHINEID='" + machid + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { Inventoryupdate, strErrMsg });
        }

        public DataTable Inventorycheck(string orgid, string machid)
        {
            string invencheck = "SELECT D_QUANTITY FROM T_INFO_INVENTORY WHERE T_INFO_INVENTORY.T_ORGID='" + orgid + "' AND T_INFO_INVENTORY.T_MACHINEID='" + machid + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { invencheck, strErrMsg });
            return dt;
        }

        #endregion

        #region 电费收入
        /// <summary>
        /// 新增电费收入
        /// </summary>
        public bool AddElectricityTariff(string stationId, string startDate, string endDate, string dueDate, float revenue, float amountPaid)
        {
            strSql = @"INSERT INTO T_INFO_ElECTRICITYTARIFF(T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_REVENUE,D_AMOUNTPAID)
                        VALUES('" + stationId + "','" + startDate + "','" + endDate + "','" + dueDate + "'," + revenue + "," + amountPaid + ")";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 修改电费收入
        /// </summary>
        public bool EditElectricityTariff(string id, string startDate, string endDate, string dueDate, float revenue, float amountPaid)
        {
            strSql = @"UPDATE T_INFO_ElECTRICITYTARIFF SET T_STARTDATE = '" + startDate + "' ,T_ENDDATE = '" + endDate + "',T_DUEDATE = '" + dueDate +
                "' ,D_REVENUE =" + revenue + " ,D_AMOUNTPAID = " + amountPaid + " WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 删除电费
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteElectricityTariff(string id)
        {
            strSql = "DELETE FROM T_INFO_ElECTRICITYTARIFF WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 补贴和证书收入
        /// <summary>
        /// 新增补贴和证书收入
        /// </summary>
        public bool AddSubsidy(string stationId, string startDate, string endDate, string dueDate, float subsidy, float amountPaid)
        {
            strSql = @"INSERT INTO T_INFO_SUBSIDY(T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_SUBSIDY,D_AMOUNTPAID)
                        VALUES('" + stationId + "','" + startDate + "','" + endDate + "','" + dueDate + "'," + subsidy + "," + amountPaid + ")";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 修改补贴和证书收入
        /// </summary>
        public bool EditSubsidy(string id, string startDate, string endDate, string dueDate, float subsidy, float amountPaid)
        {
            strSql = @"UPDATE T_INFO_SUBSIDY SET T_STARTDATE = '" + startDate + "' ,T_ENDDATE = '" + endDate + "',T_DUEDATE = '" + dueDate +
                "' ,D_SUBSIDY =" + subsidy + " ,D_AMOUNTPAID = " + amountPaid + " WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 删除补贴和证书收入
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteSubsidy(string id)
        {
            strSql = "DELETE FROM T_INFO_SUBSIDY WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 运维预付费
        /// <summary>
        /// 新增运维预付费
        /// </summary>
        public bool AddOMPrepayment(string stationId, string ContractPaymentDate, string PrepaymentDate, float ContractValue, float PrepaymentAmount)
        {
            strSql = @"INSERT INTO T_INFO_PREPAYMENTOM(T_ORGID,T_CONTRACTDATE,D_CONTRACTVALUE,T_PAYMENTDATE,D_PAYMENTAMOUNT)
                        VALUES('" + stationId + "','" + ContractPaymentDate + "'," + ContractValue + ",'" + PrepaymentDate + "'," + PrepaymentAmount + ")";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 修改运维预付费
        /// </summary>
        public bool EditOMPrepayment(string id, string ContractPaymentDate, string PrepaymentDate, float ContractValue, float PrepaymentAmount)
        {
            strSql = @"UPDATE T_INFO_PREPAYMENTOM SET T_CONTRACTDATE = '" + ContractPaymentDate + "' ,D_CONTRACTVALUE = " + ContractValue +
                " ,T_PAYMENTDATE ='" + PrepaymentDate + "' ,D_PAYMENTAMOUNT = " + PrepaymentAmount + " WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 删除运维预付费
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteOMPrepayment(string id)
        {
            strSql = "DELETE FROM T_INFO_PREPAYMENTOM WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        /// <summary>
        /// 根据电站Id和预付款支付日期判断是否重复
        /// </summary>
        /// <param name="stationId"></param>
        /// <param name="prepaymentDate"></param>
        /// <returns>true:已有记录 false:未记录</returns>
        public bool CheckIsExitOMPrepayment(string idKey, string stationId, string prepaymentDate)
        {
            strSql = @"SELECT 1 FROM T_INFO_PREPAYMENTOM WHERE T_ORGID='" + stationId + "' AND T_PAYMENTDATE='" + prepaymentDate + "'";
            if (!string.IsNullOrEmpty(idKey))
            {
                strSql += " AND ID_KEY!=" + idKey;
            }
            DataTable dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return (dt == null || dt.Rows.Count == 0) ? false : true;
        }
        #endregion

        #region 胡进财

        #region 电费收入  分页
        /// <summary>
        /// 电费收入
        /// </summary>
        /// <param name="pageCount">查询数量</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public DataTable GetElectricPage(int pageCount, int pageIndex, string orgId, DateTime st, DateTime et)
        {
            strSql = "";
            //strSql = "select top " + pageCount + " row_number() over(order by T_STARTDATE asc)as ID,ID_KEY,T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_REVENUE,";
            //strSql += "D_AMOUNTPAID from T_INFO_ElECTRICITYTARIFF where T_STARTDATE between'" + st + "' ";
            //strSql += "and '" + et + "' and ID_KEY not in(select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_INFO_ElECTRICITYTARIFF ";
            //strSql += "where T_STARTDATE between'" + st + "' and '" + et + "' order by T_STARTDATE asc) and T_ORGID='" + orgId + "' order by T_STARTDATE asc";
            strSql = "select ID_KEY,T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_REVENUE,";
            strSql += "D_AMOUNTPAID,D_REVENUE-D_AMOUNTPAID value from T_INFO_ElECTRICITYTARIFF where T_STARTDATE between '" + st + "' and '" + et + "' and T_ORGID='" + orgId + "' order by T_STARTDATE asc";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        //

        //public double GetOperationSum(string id, DateTime st, DateTime et)
        //{
        //    //strSql = "";
        //    //strSql = "select sum(D_REVENUE)-SUM(D_AMOUNTPAID)- as Value from T_INFO_ElECTRICITYTARIFF where T_ORGID='" + id + "' and T_STARTDATE between '" + st + "' and '" + et + "'";
        //    //obj = db.RunSingle(strSql, out strErrMsg);
        //    //if (obj != null)
        //    //    return Convert.ToDouble(obj);
        //    //else
        //    //    return 0;
        //}

        /// <summary>
        /// 分页数量
        /// </summary>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public int GetElectricPageCount(string orgId, DateTime st, DateTime et)
        {
            strSql = "select count(*) from T_INFO_ElECTRICITYTARIFF where T_STARTDATE between '" + st + "' and '" + et + "' and T_ORGID='" + orgId + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }

        #endregion



        #region 补贴收入 分页
        /// <summary>
        /// 补贴收入
        /// </summary>
        /// <param name="pageCount">查询数量</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public DataTable GetSubsidiesPage(int pageCount, int pageIndex, string orgId, DateTime st, DateTime et)
        {
            strSql = "";
            //strSql = "select top " + pageCount + " row_number() over(order by T_STARTDATE asc)as ID,ID_KEY,T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_SUBSIDY,";
            //strSql += "D_AMOUNTPAID from T_INFO_SUBSIDY,D_SUBSIDY-D_AMOUNTPAID where T_STARTDATE between '" + st + "' ";
            //strSql += "and '" + et + "' and ID_KEY not in(select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_INFO_SUBSIDY ";
            //strSql += "where T_STARTDATE between '" + st + "' and '" + et + "' order by T_STARTDATE asc) and T_ORGID='" + orgId + "' order by T_STARTDATE asc";

            strSql += "select ID_KEY,T_ORGID,T_STARTDATE,T_ENDDATE,T_DUEDATE,D_SUBSIDY,";
            strSql += "D_AMOUNTPAID,D_SUBSIDY-D_AMOUNTPAID value from T_INFO_SUBSIDY where T_ORGID='" + orgId + "' and T_STARTDATE between '" + st + "' and '" + et + "' order by T_STARTDATE asc";

            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        /// <summary>
        /// 补贴收入 分页数量
        /// </summary>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public int GetSubsidiesPageCount(string orgId, DateTime st, DateTime et)
        {
            strSql = "select count(*) from T_INFO_SUBSIDY where T_STARTDATE between '" + st + "' and '" + et + "' and T_ORGID='" + orgId + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 运维预付费用
        /// <summary>
        /// 补贴收入
        /// </summary>
        /// <param name="pageCount">查询数量</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public DataTable GetPepaymentomPage(int pageCount, int pageIndex, string orgId, DateTime st, DateTime et)
        {
            strSql = "";
            strSql = "select top " + pageCount + " row_number() over(order by T_PAYMENTDATE asc)as ID,ID_KEY,T_ORGID,T_CONTRACTDATE,D_CONTRACTVALUE,";
            strSql += "T_PAYMENTDATE,D_PAYMENTAMOUNT from T_INFO_PREPAYMENTOM where T_PAYMENTDATE between '" + st + "' ";
            strSql += "and '" + et + "' and ID_KEY not in(select top " + (pageCount * (pageIndex - 1)) + " ID_KEY from T_INFO_PREPAYMENTOM";
            strSql += " where T_PAYMENTDATE between '" + st + "' and '" + et + "' order by T_PAYMENTDATE asc) and T_ORGID='" + orgId + "' order by T_PAYMENTDATE asc ";

            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        /// <summary>
        /// 补贴收入 分页数量
        /// </summary>
        /// <param name="orgId">电站编码</param>
        /// <param name="st">开始时间</param>
        /// <param name="et">结束时间</param>
        /// <returns></returns>
        public int GetPepaymentomPageCount(string orgId, DateTime st, DateTime et)
        {
            strSql = "select count(*) from T_INFO_SUBSIDY where T_PAYMENTDATE between'" + st + "' and '" + et + "' and T_ORGID='" + orgId + "'";
            obj = iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg });
            return Convert.ToInt32(obj);
        }
        #endregion

        #endregion

        #region 设备预付款
        public bool AddEquipmentPrepayment(string orgId, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPrriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"INSERT INTO T_INFO_PREPAYMENTEQ (T_ORGID,T_TIME ,T_USERNAME ,D_QUANTITY ,T_MACHINEID ,D_PRICE ,
                        T_WTDATE ,D_WTPERIOD ,T_CONTACTNAME ,T_CONTACTPHONE ,T_CONTACTEMAIL) VALUES ('" + orgId + "','" +
                       purchaseTime + "','" + userName + "'," + quantity + ",'" + machineId + "'," + price + ",'" + warrantyDate + "'," +
                       warrantyPrriod + ",'" + contractName + "','" + contractPhone + "','" + contractEmail + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdateEquipmentPrepayment(string idKey, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPeriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"UPDATE T_INFO_PREPAYMENTEQ  SET T_TIME='" + purchaseTime + "',T_USERNAME='" + userName + "',D_QUANTITY=" + quantity +
                ",T_MACHINEID='" + machineId + "',D_PRICE=" + price + " ,T_WTDATE='" + warrantyDate + "',D_WTPERIOD=" + warrantyPeriod +
                ",T_CONTACTNAME='" + contractName + "',T_CONTACTPHONE='" + contractPhone + "', T_CONTACTEMAIL='" + contractEmail + "'" +
                    "WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeleteEquipmentPrepayment(string id)
        {
            strSql = "DELETE FROM T_INFO_PREPAYMENTEQ WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 设备更换
        public bool AddEquipmentReplace(string orgId, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPrriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"INSERT INTO T_INFO_EQUIPMENTREPLACE (T_ORGID,T_TIME ,T_USERNAME ,D_QUANTITY ,T_MACHINEID ,D_PRICE ,
                        T_WTDATE ,D_WTPERIOD ,T_CONTACTNAME ,T_CONTACTPHONE ,T_CONTACTEMAIL) VALUES ('" + orgId + "','" +
                       purchaseTime + "','" + userName + "'," + quantity + ",'" + machineId + "'," + price + ",'" + warrantyDate + "'," +
                       warrantyPrriod + ",'" + contractName + "','" + contractPhone + "','" + contractEmail + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdateEquipmentReplace(string idKey, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPeriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"UPDATE T_INFO_EQUIPMENTREPLACE  SET T_TIME='" + purchaseTime + "',T_USERNAME='" + userName + "',D_QUANTITY=" + quantity +
                ",T_MACHINEID='" + machineId + "',D_PRICE=" + price + " ,T_WTDATE='" + warrantyDate + "',D_WTPERIOD=" + warrantyPeriod +
                ",T_CONTACTNAME='" + contractName + "',T_CONTACTPHONE='" + contractPhone + "', T_CONTACTEMAIL='" + contractEmail + "'" +
                    "WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeleteEquipmentReplace(string id)
        {
            strSql = "DELETE FROM T_INFO_EQUIPMENTREPLACE WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 初始资本支出（初始固定资产）
        public bool AddInitialCapex(string orgId, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPrriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"INSERT INTO T_INFO_INITIALCAPEX (T_ORGID,T_TIME ,T_USERNAME ,D_QUANTITY ,T_MACHINEID ,D_PRICE ,
                        T_WTDATE ,D_WTPERIOD ,T_CONTACTNAME ,T_CONTACTPHONE ,T_CONTACTEMAIL) VALUES ('" + orgId + "','" +
                       purchaseTime + "','" + userName + "'," + quantity + ",'" + machineId + "'," + price + ",'" + warrantyDate + "'," +
                       warrantyPrriod + ",'" + contractName + "','" + contractPhone + "','" + contractEmail + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdateInitialCapex(string idKey, string userName, string purchaseTime, string machineId, int quantity, float price, float warrantyPeriod, string warrantyDate, string contractName, string contractPhone, string contractEmail)
        {
            strSql = @"UPDATE T_INFO_INITIALCAPEX  SET T_TIME='" + purchaseTime + "',T_USERNAME='" + userName + "',D_QUANTITY=" + quantity +
                ",T_MACHINEID='" + machineId + "',D_PRICE=" + price + " ,T_WTDATE='" + warrantyDate + "',D_WTPERIOD=" + warrantyPeriod +
                ",T_CONTACTNAME='" + contractName + "',T_CONTACTPHONE='" + contractPhone + "', T_CONTACTEMAIL='" + contractEmail + "'" +
                    "WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeleteInitialCapex(string id)
        {
            strSql = "DELETE FROM T_INFO_INITIALCAPEX WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion


        #region 设备采购
        public bool AddEquipmentProcure(string orgId, int quantity, string machineId, string purchaseDate, float paymentAmount, string dueDate)
        {
            strSql = @"INSERT INTO T_INFO_EQUIPMENTPROCUREMENT (T_ORGID ,D_QUANTITY ,T_MACHINEID ,T_PUCHASINGDATE , D_PAYMENTAMOUNT ,T_DUEDATE )
                        VALUES ('" + orgId + "'," + quantity + ",'" + machineId + "','" + purchaseDate + "'," + paymentAmount + ",'" + dueDate + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdateEquipmentProcure(string idKey, int quantity, string machineId, string purchaseDate, float paymentAmount, string dueDate)
        {
            strSql = @"UPDATE T_INFO_EQUIPMENTPROCUREMENT SET D_QUANTITY=" + quantity + ",T_MACHINEID='" + machineId +
                      "',T_PUCHASINGDATE='" + purchaseDate + "' ,D_PAYMENTAMOUNT=" + paymentAmount + ",T_DUEDATE='" + dueDate + "'" +
                    "WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeleteEquipmentProcure(string id)
        {
            strSql = "DELETE FROM T_INFO_EQUIPMENTPROCUREMENT WHERE ID_KEY=" + id;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 工资福利
        public bool AddSalaryWelfare(string orgId, string typeId, float paymentAmount, string dueDate)
        {
            strSql = @"INSERT INTO T_INFO_SALARYWELFARE (T_ORGID,T_TYPEID,D_PAYMENTAMOUNT,T_DUEDATE)
                        VALUES('" + orgId + "','" + typeId + "'," + paymentAmount + ",'" + dueDate + "')";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdateSalaryWelfare(string idKey, string typeId, float paymentAmount, string dueDate)
        {
            strSql = @"UPDATE T_INFO_SALARYWELFARE SET T_TYPEID='" + typeId + "',D_PAYMENTAMOUNT=" + paymentAmount + ",T_DUEDATE='" + dueDate + "' "
                        + "WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeleteSalaryWelfare(string idKey)
        {
            strSql = @"DELETE FROM T_INFO_SALARYWELFARE WHERE ID_KEY=" + idKey;
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public DataTable GetSalaryWelfareType()
        {
            strSql = @"SELECT T_TYPEID ,T_TYPEDESC_EN ,T_TYPEDESC_CN FROM T_BASE_SALARYTYPE";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }
        #endregion


        #region 设备信息
        /// <summary>
        /// 获取设备类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetEquipmentType()
        {
            strSql = @"SELECT ID_KEY,T_TYPEID,T_TYPEDESC_EN,T_TYPEDESC_CN FROM T_BASE_EQUIPTYPE";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }
        /// <summary>
        /// 获取设备厂家
        /// </summary>
        /// <returns></returns>
        public DataTable GetEquipmentCompany(string typeId)
        {
            strSql = @"SELECT A.ID_KEY,A.T_COMPANYID,A.T_COMPANYDESC_EN,A.T_COMPANYDESC_CN FROM T_BASE_MACHINECOMPANY A
                        INNER JOIN T_BASE_MACHINE B ON A.T_COMPANYID=B.T_COMPANYID
                        WHERE B.T_TYPEID='" + typeId + "'";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }
        /// <summary>
        /// 获取设备类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetEquipmentModel(string machineTypeId, string machineCompanyId)
        {
            strSql = @"SELECT ID_KEY,T_COMPANYID,T_MACHINEID,D_RL,T_TYPEID FROM T_BASE_MACHINE WHERE T_TYPEID='" + machineTypeId + "' AND T_COMPANYID='" + machineCompanyId + "'";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region Instock, Outstock 下拉框

        public DataTable GetEquiptypeForInstock()
        {
            strSql = @"SELECT T_TYPEID, T_TYPEDESC_EN, T_TYPEDESC_CN FROM T_BASE_EQUIPTYPE";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        public DataTable GetEquiptypeForOutstock()
        {
            strSql = @"SELECT T_TYPEID, T_TYPEDESC_EN, T_TYPEDESC_CN FROM T_BASE_EQUIPTYPE";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        public DataTable GetManufactoryForInstock(string typeid)
        {
            strSql = @"SELECT T_COMPANYID, T_COMPANYDESC_EN, T_COMPANYDESC_CN FROM T_BASE_MACHINECOMPANY WHERE T_BASE_MACHINECOMPANY.T_COMPANYID in (SELECT T_COMPANYID from T_BASE_MACHINE WHERE T_BASE_MACHINE.T_TYPEID='" + typeid + "')";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        public DataTable GetManufactoryForOutstock(string typeid)
        {
            strSql = @"SELECT T_COMPANYID, T_COMPANYDESC_EN, T_COMPANYDESC_CN FROM T_BASE_MACHINECOMPANY WHERE T_BASE_MACHINECOMPANY.T_COMPANYID in (SELECT T_COMPANYID from T_BASE_MACHINE WHERE T_BASE_MACHINE.T_TYPEID='" + typeid + "')";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        public DataTable GetUnitTypeForInstock(string companyid, string typeid)
        {
            strSql = @"SELECT T_MACHINEID FROM T_BASE_MACHINE WHERE T_COMPANYID='" + companyid + "' AND T_TYPEID='" + typeid + "'";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }
        public DataTable GetUnitTypeForOutstock(string companyid, string typeid)
        {
            strSql = @"SELECT T_MACHINEID FROM T_BASE_MACHINE WHERE T_COMPANYID='" + companyid + "' AND T_TYPEID='" + typeid + "'";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        #endregion
    }
}
